pandas is a Python package providing fast, flexible, and expressive data structures designed to work with relational or labeled data both. It is a fundamental high-level building block for doing practical, real world data analysis in Python.
pandas is well suited for:
Key features:
In [1]:
import pandas as pd
import numpy as np
In [2]:
counts = pd.Series([632, 1638, 569, 115])
counts
Out[2]:
If an index is not specified, a default sequence of integers is assigned as the index. A NumPy array comprises the values of the Series, while the index is a pandas Index object.
In [3]:
counts.values
Out[3]:
In [4]:
counts.index
Out[4]:
We can assign meaningful labels to the index, if they are available:
In [5]:
bacteria = pd.Series([632, 1638, 569, 115],
index=['Firmicutes', 'Proteobacteria', 'Actinobacteria', 'Bacteroidetes'])
bacteria
Out[5]:
These labels can be used to refer to the values in the Series.
In [6]:
bacteria['Actinobacteria']
Out[6]:
In [7]:
bacteria[[name.endswith('bacteria') for name in bacteria.index]]
Out[7]:
In [8]:
[name.endswith('bacteria') for name in bacteria.index]
Out[8]:
Notice that the indexing operation preserved the association between the values and the corresponding indices.
We can still use positional indexing if we wish.
In [9]:
bacteria[0]
Out[9]:
NumPy's math functions and other operations can be applied to Series without losing the data structure.
In [10]:
np.log(bacteria)
Out[10]:
We can also filter according to the values in the Series:
In [11]:
bacteria[bacteria>1000]
Out[11]:
A Series can be thought of as an ordered key-value store. In fact, we can create one from a dict:
In [12]:
bacteria_dict = {'Firmicutes': 632, 'Proteobacteria': 1638, 'Actinobacteria': 569, 'Bacteroidetes': 115}
pd.Series(bacteria_dict)
Out[12]:
Notice that the Series is created in key-sorted order.
If we pass a custom index to Series, it will select the corresponding values from the dict, and treat indices without corrsponding values as missing. Pandas uses the NaN (not a number) type for missing values.
In [13]:
bacteria2 = pd.Series(bacteria_dict, index=['Cyanobacteria','Firmicutes','Proteobacteria','Actinobacteria'])
bacteria2
Out[13]:
In [14]:
bacteria2.isnull()
Out[14]:
Critically, the labels are used to align data when used in operations with other Series objects:
In [15]:
bacteria + bacteria2
Out[15]:
Contrast this with NumPy arrays, where arrays of the same length will combine values element-wise; adding Series combined values with the same label in the resulting series. Notice also that the missing values were propogated by addition.
Inevitably, we want to be able to store, view and manipulate data that is multivariate, where for every index there are multiple fields or columns of data, often of varying data type.
A DataFrame is a tabular data structure, encapsulating multiple series like columns in a spreadsheet. Data are stored internally as a 2-dimensional object, but the DataFrame allows us to represent and manipulate higher-dimensional data.
In [16]:
data = pd.DataFrame({'value':[632, 1638, 569, 115, 433, 1130, 754, 555],
'patient':[1, 1, 1, 1, 2, 2, 2, 2],
'phylum':['Firmicutes', 'Proteobacteria', 'Actinobacteria',
'Bacteroidetes', 'Firmicutes', 'Proteobacteria', 'Actinobacteria', 'Bacteroidetes']})
data
Out[16]:
Notice the DataFrame is sorted by column name. We can change the order by indexing them in the order we desire:
In [17]:
data[['phylum','value','patient']]
Out[17]:
A DataFrame has a second index, representing the columns:
In [18]:
data.columns
Out[18]:
If we wish to access columns, we can do so either by dict-like indexing or by attribute:
In [19]:
data['value']
Out[19]:
In [20]:
data.value
Out[20]:
In [21]:
type(data.value)
Out[21]:
In [22]:
type(data[['value']])
Out[22]:
Notice this is different than with Series, where dict-like indexing retrieved a particular element (row).
If we want access to a row in a DataFrame, we index its ix attribute.
In [23]:
data.ix[3]
Out[23]:
Its important to note that the Series returned when a DataFrame is indexted is merely a view on the DataFrame, and not a copy of the data itself. So you must be cautious when manipulating this data:
In [24]:
vals = data.value
vals
Out[24]:
In [25]:
vals[5] = 0
vals
Out[25]:
In [26]:
data
Out[26]:
If we plan on modifying an extracted Series, its a good idea to make a copy.
In [27]:
vals = data.value.copy()
vals[5] = 1000
data
Out[27]:
We can create or modify columns by assignment:
In [28]:
data.value[3] = 14
data
Out[28]:
In [29]:
data['year'] = 2013
data
Out[29]:
We can use del to remove columns, in the same way dict entries can be removed:
In [33]:
del data['year']
data
Out[33]:
We can extract the underlying data as a simple ndarray by accessing the values attribute:
In [34]:
data.values
Out[34]:
Pandas uses a custom data structure to represent the indices of Series and DataFrames.
In [35]:
data.index
Out[35]:
Index objects are immutable:
In [36]:
data.index[0] = 15
This is so that Index objects can be shared between data structures without fear that they will be changed.
In [37]:
bacteria2.index = bacteria.index
In [38]:
bacteria2
Out[38]:
A key, but often under-appreciated, step in data analysis is importing the data that we wish to analyze. Though it is easy to load basic data structures into Python using built-in tools or those provided by packages like NumPy, it is non-trivial to import structured data well, and to easily convert this input into a robust data structure:
genes = np.loadtxt("genes.csv", delimiter=",", dtype=[('gene', '|S10'), ('value', '<f4')])
Pandas provides a convenient set of functions for importing tabular data in a number of formats directly into a DataFrame object. These functions include a slew of options to perform type inference, indexing, parsing, iterating and cleaning automatically as data are imported.
Let's start with some more bacteria data, stored in csv format.
In [39]:
!cat data/microbiome.csv
This table can be read into a DataFrame using read_csv:
In [40]:
mb = pd.read_csv("data/microbiome.csv")
mb
Out[40]:
Notice that read_csv automatically considered the first row in the file to be a header row.
read_csv is just a convenience function for read_table, since csv is such a common format:
In [42]:
mb = pd.read_table("data/microbiome.csv", sep=',')
The sep argument can be customized as needed to accomodate arbitrary separators. For example, we can use a regular expression to define a variable amount of whitespace, which is unfortunately very common in some data formats:
sep='\s+'For a more useful index, we can specify the first two columns, which together provide a unique index to the data.
In [44]:
mb = pd.read_csv("data/microbiome.csv", index_col=['Taxon','Patient'])
mb.head()
Out[44]:
This is called a hierarchical index, which we will revisit later in the section.
If we only want to import a small number of rows from, say, a very large data file we can use nrows:
In [46]:
pd.read_csv("data/microbiome.csv", nrows=4)
Out[46]:
Alternately, if we want to process our data in reasonable chunks, the chunksize argument will return an iterable object that can be employed in a data processing loop. For example, our microbiome data are organized by bacterial phylum, with 15 patients represented in each:
In [47]:
data_chunks = pd.read_csv("data/microbiome.csv", chunksize=15)
mean_tissue = {chunk.Taxon[0]: chunk.Tissue.mean() for chunk in data_chunks}
mean_tissue
Out[47]:
Most real-world data is incomplete, with values missing due to incomplete observation, data entry or transcription error, or other reasons. Pandas will automatically recognize and parse common missing data indicators, including NA and NULL.
In [48]:
!cat data/microbiome_missing.csv
In [61]:
pd.read_csv("data/microbiome_missing.csv").head(20)
Out[61]:
Above, Pandas recognized NA and an empty field as missing data.
In [49]:
pd.isnull(pd.read_csv("data/microbiome_missing.csv")).head(20)
Out[49]:
Unfortunately, there will sometimes be inconsistency with the conventions for missing data. In this example, there is a question mark "?" and a large negative number where there should have been a positive integer. We can specify additional symbols with the na_values argument:
In [63]:
pd.read_csv("data/microbiome_missing.csv", na_values=['?', -99999]).head(20)
Out[63]:
These can be specified on a column-wise basis using an appropriate dict as the argument for na_values.
Since so much financial and scientific data ends up in Excel spreadsheets (regrettably), Pandas' ability to directly import Excel spreadsheets is valuable. This support is contingent on having one or two dependencies (depending on what version of Excel file is being imported) installed: xlrd and openpyxl (these may be installed with either pip or easy_install).
The read_excel function in Pandas imports individual sheets from Excel spreadhseets into a DataFrame:
In [51]:
mb1 = pd.read_excel('data/microbiome/MID2.xls', sheetname='Sheet 1', header=None)
mb1.head()
Out[51]:
There are several other data formats that can be imported into Python and converted into DataFrames, with the help of buitl-in or third-party libraries. These include JSON, XML, HDF5, relational and non-relational databases, and various web APIs. These are beyond the scope of this tutorial, but are covered in Python for Data Analysis.
This section introduces the new user to the key functionality of Pandas that is required to use the software effectively.
For some variety, we will leave our digestive tract bacteria behind and employ some baseball data.
In [53]:
baseball = pd.read_csv("data/baseball.csv", index_col='id')
baseball.head()
Out[53]:
In [54]:
baseball.reindex(baseball.index[::-1]).head()
Out[54]:
Notice that the id index is not sequential. Say we wanted to populate the table with every id value. We could specify and index that is a sequence from the first to the last id numbers in the database, and Pandas would fill in the missing data with NaN values:
In [55]:
id_range = range(baseball.index.values.min(), baseball.index.values.max())
baseball.reindex(id_range).head()
Out[55]:
Missing values can be filled as desired, either with selected values, or by rule:
In [56]:
baseball.reindex(id_range, method='ffill', columns=['player','year']).head()
Out[56]:
In [57]:
baseball.reindex(id_range, fill_value='charliebrown', columns=['player']).head()
Out[57]:
We can remove rows or columns via the drop method:
In [58]:
baseball.shape
Out[58]:
In [60]:
baseball.drop([89525, 89526]).head(10)
Out[60]:
In [61]:
baseball.drop(['ibb','hbp'], axis=1).head()
Out[61]:
In [63]:
# Sample Series object
hits = baseball.h
hits
Out[63]:
In [64]:
# Numpy-style indexing
hits[:3]
Out[64]:
In [66]:
# Indexing by label
hits[[89523,89533]]
Out[66]:
In a DataFrame we can slice along either or both axes:
In [68]:
baseball[['h','ab']].head()
Out[68]:
In [70]:
baseball[baseball.ab>500]
Out[70]:
For a more concise (and readable) syntax, we can use the new query method to perform selection on a DataFrame. Instead of having to type the fully-specified column, we can simply pass a string that describes what to select. The query above is then simply:
In [71]:
baseball.query('ab > 500')
Out[71]:
The DataFrame.index and DataFrame.columns are placed in the query namespace by default. If you want to refer to a variable in the current namespace, you can prefix the variable with @:
In [72]:
min_ab = 450
In [73]:
baseball.query('ab > @min_ab')
Out[73]:
The indexing field ix allows us to select subsets of rows and columns in an intuitive way:
In [74]:
baseball.ix[89462, ['h','X2b', 'X3b', 'hr']]
Out[74]:
In [77]:
baseball.ix[89462:89466, 5:8]
Out[77]:
DataFrame and Series objects allow for several operations to take place either on a single object, or between two or more objects.
For example, we can perform arithmetic on the elements of two objects, such as combining baseball statistics across years. First, let's (artificially) construct two Series, consisting of home runs hit in years 2006 and 2007, respectively:
In [78]:
hr2006 = baseball.ix[baseball.year==2006, 'hr']
hr2006.index = baseball.player[baseball.year==2006]
hr2007 = baseball.ix[baseball.year==2007, 'hr']
hr2007.index = baseball.player[baseball.year==2007]
Now, let's add them together, in hopes of getting 2-year home run totals:
In [79]:
hr_total = hr2006 + hr2007
hr_total
Out[79]:
Pandas' data alignment places NaN values for labels that do not overlap in the two Series. In fact, there are only 6 players that occur in both years.
In [80]:
hr_total[hr_total.notnull()]
Out[80]:
While we do want the operation to honor the data labels in this way, we probably do not want the missing values to be filled with NaN. We can use the add method to calculate player home run totals by using the fill_value argument to insert a zero for home runs where labels do not overlap:
In [81]:
hr2007.add(hr2006, fill_value=0)
Out[81]:
Operations can also be broadcast between rows or columns.
For example, if we subtract the maximum number of home runs hit from the hr column, we get how many fewer than the maximum were hit by each player:
In [82]:
baseball.hr - baseball.hr.max()
Out[82]:
Or, looking at things row-wise, we can see how a particular player compares with the rest of the group with respect to important statistics
In [83]:
baseball.ix[89521, "player"]
Out[83]:
In [84]:
stats = baseball[['h','X2b', 'X3b', 'hr']]
diff = stats - stats.ix[89521]
diff[:10]
Out[84]:
We can also apply functions to each column or row of a DataFrame
In [85]:
stats.apply(np.median)
Out[85]:
In [86]:
stat_range = lambda x: x.max() - x.min()
stats.apply(stat_range)
Out[86]:
Lets use apply to calculate a meaningful baseball statistics, slugging percentage:
$$SLG = \frac{1B + (2 \times 2B) + (3 \times 3B) + (4 \times HR)}{AB}$$And just for fun, we will format the resulting estimate.
In [87]:
def slugging(x):
bases = x['h']-x['X2b']-x['X3b']-x['hr'] + 2*x['X2b'] + 3*x['X3b'] + 4*x['hr']
ab = x['ab']+1e-6
return bases/ab
baseball.apply(slugging, axis=1).round(3)
Out[87]:
In [ ]:
In [90]:
baseball_h = baseball.set_index(['year', 'team', 'player'])
baseball_h.head(10)
Out[90]:
This index is a MultiIndex object that consists of a sequence of tuples, the elements of which is some combination of the three columns used to create the index. Where there are multiple repeated values, Pandas does not print the repeats, making it easy to identify groups of values.
In [91]:
baseball_h.index[:10]
Out[91]:
In [92]:
baseball_h.index.is_unique
Out[92]:
In [93]:
baseball_h.ix[(2007, 'ATL', 'francju01')]
Out[93]:
Recall earlier we imported some microbiome data using two index columns. This created a 2-level hierarchical index:
In [94]:
mb = pd.read_csv("data/microbiome.csv", index_col=['Taxon','Patient'])
In [95]:
mb.head(10)
Out[95]:
In [96]:
mb.index
Out[96]:
With a hierachical index, we can select subsets of the data based on a partial index:
In [97]:
mb.ix['Proteobacteria']
Out[97]:
Hierarchical indices can be created on either or both axes. Here is a trivial example:
In [98]:
frame = pd.DataFrame(np.arange(12).reshape(( 4, 3)),
index =[['a', 'a', 'b', 'b'], [1, 2, 1, 2]],
columns =[['Ohio', 'Ohio', 'Colorado'], ['Green', 'Red', 'Green']])
frame
Out[98]:
Additionally, the order of the set of indices in a hierarchical MultiIndex can be changed by swapping them pairwise:
In [100]:
mb.swaplevel('Patient', 'Taxon').head()
Out[100]:
The occurence of missing data is so prevalent that it pays to use tools like Pandas, which seamlessly integrates missing data handling so that it can be dealt with easily, and in the manner required by the analysis at hand.
Missing data are represented in Series and DataFrame objects by the NaN floating point value. However, None is also treated as missing, since it is commonly used as such in other contexts (e.g. NumPy).
In [101]:
foo = pd.Series([np.nan, -3, None, 'foobar'])
foo
Out[101]:
In [102]:
foo.isnull()
Out[102]:
Missing values may be dropped or indexed out:
In [103]:
bacteria2
Out[103]:
In [104]:
bacteria2.dropna()
Out[104]:
In [105]:
bacteria2[bacteria2.notnull()]
Out[105]:
By default, dropna drops entire rows in which one or more values are missing.
In [106]:
data
Out[106]:
In [107]:
data.dropna()
Out[107]:
This can be overridden by passing the how='all' argument, which only drops a row when every field is a missing value.
In [108]:
data.dropna(how='all')
Out[108]:
This can be customized further by specifying how many values need to be present before a row is dropped via the thresh argument.
In [109]:
data.ix[7, 'year'] = np.nan
data
Out[109]:
In [110]:
data.dropna(thresh=4)
Out[110]:
This is typically used in time series applications, where there are repeated measurements that are incomplete for some subjects.
If we want to drop missing values column-wise instead of row-wise, we use axis=1.
In [111]:
data.dropna(axis=1)
Out[111]:
Rather than omitting missing data from an analysis, in some cases it may be suitable to fill the missing value in, either with a default value (such as zero) or a value that is either imputed or carried forward/backward from similar data points. We can do this programmatically in Pandas with the fillna argument.
In [112]:
bacteria2.fillna(0)
Out[112]:
In [113]:
data.fillna({'year': 2013, 'treatment':2})
Out[113]:
Notice that fillna by default returns a new object with the desired filling behavior, rather than changing the Series or DataFrame in place (in general, we like to do this, by the way!).
In [114]:
data
Out[114]:
We can alter values in-place using inplace=True.
In [115]:
data.year.fillna(2013, inplace=True)
data
Out[115]:
Missing values can also be interpolated, using any one of a variety of methods:
In [116]:
bacteria2.fillna(method='bfill')
Out[116]:
In [117]:
bacteria2.fillna(bacteria2.mean())
Out[117]:
We often wish to summarize data in Series or DataFrame objects, so that they can more easily be understood or compared with similar data. The NumPy package contains several functions that are useful here, but several summarization or reduction methods are built into Pandas data structures.
In [118]:
baseball.sum()
Out[118]:
Clearly, sum is more meaningful for some columns than others. For methods like mean for which application to string variables is not just meaningless, but impossible, these columns are automatically exculded:
In [119]:
baseball.mean()
Out[119]:
The important difference between NumPy's functions and Pandas' methods is that the latter have built-in support for handling missing data.
In [120]:
bacteria2
Out[120]:
In [121]:
bacteria2.mean()
Out[121]:
Sometimes we may not want to ignore missing values, and allow the nan to propagate.
In [122]:
bacteria2.mean(skipna=False)
Out[122]:
Passing axis=1 will summarize over rows instead of columns, which only makes sense in certain situations.
In [124]:
extra_bases = baseball[['X2b','X3b','hr']].sum(axis=1)
extra_bases.sort_values(ascending=False)
Out[124]:
A useful summarization that gives a quick snapshot of multiple statistics for a Series or DataFrame is describe:
In [125]:
baseball.describe()
Out[125]:
describe can detect non-numeric data and sometimes yield useful information about it.
In [126]:
baseball.player.describe()
Out[126]:
We can also calculate summary statistics across multiple columns, for example, correlation and covariance.
$$cov(x,y) = \sum_i (x_i - \bar{x})(y_i - \bar{y})$$
In [127]:
baseball.hr.cov(baseball.X2b)
Out[127]:
In [128]:
baseball.hr.corr(baseball.X2b)
Out[128]:
In [129]:
baseball.ab.corr(baseball.h)
Out[129]:
In [130]:
baseball.corr()
Out[130]:
If we have a DataFrame with a hierarchical index (or indices), summary statistics can be applied with respect to any of the index levels:
In [131]:
mb.head()
Out[131]:
In [132]:
mb.sum(level='Taxon')
Out[132]:
The data/ebola folder contains summarized reports of Ebola cases from three countries during the recent outbreak of the disease in West Africa. For each country, there are daily reports that contain various information about the outbreak in several cities in each country.
From these data files, use pandas to import them and create a single data frame that includes the daily totals of new cases and deaths for each country.
We may use this compiled data for more advaned applications later in the course.
In [ ]:
# Write your answer here
Date and time data are inherently problematic. There are an unequal number of days in every month, an unequal number of days in a year (due to leap years), and time zones that vary over space. Yet information about time is essential in many analyses, particularly in the case of time series analysis.
The datetime built-in library handles temporal information down to the nanosecond.
In [133]:
from datetime import datetime
In [134]:
now = datetime.now()
now
Out[134]:
In [135]:
now.day
Out[135]:
In [136]:
now.weekday()
Out[136]:
In addition to datetime there are simpler objects for date and time information only, respectively.
In [137]:
from datetime import date, time
In [138]:
time(3, 24)
Out[138]:
In [139]:
date(1970, 9, 3)
Out[139]:
Having a custom data type for dates and times is convenient because we can perform operations on them easily. For example, we may want to calculate the difference between two times:
In [140]:
my_age = now - datetime(1970, 9, 3)
my_age
Out[140]:
In [141]:
my_age.days/365.
Out[141]:
In this section, we will manipulate data collected from ocean-going vessels on the eastern seaboard. Vessel operations are monitored using the Automatic Identification System (AIS), a safety at sea navigation technology which vessels are required to maintain and that uses transponders to transmit very high frequency (VHF) radio signals containing static information including ship name, call sign, and country of origin, as well as dynamic information unique to a particular voyage such as vessel location, heading, and speed.
The International Maritime Organization’s (IMO) International Convention for the Safety of Life at Sea requires functioning AIS capabilities on all vessels 300 gross tons or greater and the US Coast Guard requires AIS on nearly all vessels sailing in U.S. waters. The Coast Guard has established a national network of AIS receivers that provides coverage of nearly all U.S. waters. AIS signals are transmitted several times each minute and the network is capable of handling thousands of reports per minute and updates as often as every two seconds. Therefore, a typical voyage in our study might include the transmission of hundreds or thousands of AIS encoded signals. This provides a rich source of spatial data that includes both spatial and temporal information.
For our purposes, we will use summarized data that describes the transit of a given vessel through a particular administrative area. The data includes the start and end time of the transit segment, as well as information about the speed of the vessel, how far it travelled, etc.
In [143]:
segments = pd.read_csv("data/AIS/transit_segments.csv")
segments.head()
Out[143]:
For example, we might be interested in the distribution of transit lengths, so we can plot them as a histogram:
In [145]:
%matplotlib inline
segments.seg_length.hist(bins=500)
Out[145]:
Though most of the transits appear to be short, there are a few longer distances that make the plot difficult to read. This is where a transformation is useful:
In [146]:
segments.seg_length.apply(np.log).hist(bins=500)
Out[146]:
We can see that although there are date/time fields in the dataset, they are not in any specialized format, such as datetime.
In [147]:
segments.st_time.dtype
Out[147]:
Our first order of business will be to convert these data to datetime. The strptime method parses a string representation of a date and/or time field, according to the expected format of this information.
In [148]:
datetime.strptime(segments.st_time.ix[0], '%m/%d/%y %H:%M')
Out[148]:
The dateutil package includes a parser that attempts to detect the format of the date strings, and convert them automatically.
In [150]:
parse(segments.st_time.ix[0])
Out[150]:
As a convenience, Pandas has a to_datetime method that will parse and convert an entire Series of formatted strings into datetime objects.
In [152]:
pd.to_datetime(segments.st_time[:10])
Out[152]:
Pandas also has a custom NA value for missing datetime objects, NaT.
In [153]:
pd.to_datetime([None])
Out[153]:
Also, if to_datetime() has problems parsing any particular date/time format, you can pass the spec in using the format= argument.
Now that we have the vessel transit information as we need it, we may want a little more information regarding the vessels themselves. In the data/AIS folder there is a second table that contains information about each of the ships that traveled the segments in the segments table.
In [154]:
vessels = pd.read_csv("data/AIS/vessel_information.csv", index_col='mmsi')
vessels.head()
Out[154]:
In [155]:
[v for v in vessels.type.unique() if v.find('/')==-1]
Out[155]:
In [156]:
vessels.type.value_counts()
Out[156]:
The challenge, however, is that several ships have travelled multiple segments, so there is not a one-to-one relationship between the rows of the two tables. The table of vessel information has a one-to-many relationship with the segments.
In Pandas, we can combine tables according to the value of one or more keys that are used to identify rows, much like an index. Using a trivial example:
In [157]:
df1 = pd.DataFrame(dict(id=range(4), age=np.random.randint(18, 31, size=4)))
df2 = pd.DataFrame(dict(id=list(range(3))+list(range(3)),
score=np.random.random(size=6)))
df1
Out[157]:
In [158]:
df2
Out[158]:
In [159]:
pd.merge(df1, df2)
Out[159]:
Notice that without any information about which column to use as a key, Pandas did the right thing and used the id column in both tables. Unless specified otherwise, merge will used any common column names as keys for merging the tables.
Notice also that id=3 from df1 was omitted from the merged table. This is because, by default, merge performs an inner join on the tables, meaning that the merged table represents an intersection of the two tables.
In [160]:
pd.merge(df1, df2, how='outer')
Out[160]:
The outer join above yields the union of the two tables, so all rows are represented, with missing values inserted as appropriate. One can also perform right and left joins to include all rows of the right or left table (i.e. first or second argument to merge), but not necessarily the other.
Looking at the two datasets that we wish to merge:
In [161]:
segments.head(1)
Out[161]:
In [162]:
vessels.head(1)
Out[162]:
we see that there is a mmsi value (a vessel identifier) in each table, but it is used as an index for the vessels table. In this case, we have to specify to join on the index for this table, and on the mmsi column for the other.
In [163]:
segments_merged = pd.merge(vessels, segments, left_index=True, right_on='mmsi')
In [164]:
segments_merged.head()
Out[164]:
In this case, the default inner join is suitable; we are not interested in observations from either table that do not have corresponding entries in the other.
Notice that mmsi field that was an index on the vessels table is no longer an index on the merged table.
Here, we used the merge function to perform the merge; we could also have used the merge method for either of the tables:
In [165]:
vessels.merge(segments, left_index=True, right_on='mmsi').head()
Out[165]:
Occasionally, there will be fields with the same in both tables that we do not wish to use to join the tables; they may contain different information, despite having the same name. In this case, Pandas will by default append suffixes _x and _y to the columns to uniquely identify them.
In [166]:
segments['type'] = 'foo'
pd.merge(vessels, segments, left_index=True, right_on='mmsi').head()
Out[166]:
This behavior can be overridden by specifying a suffixes argument, containing a list of the suffixes to be used for the columns of the left and right columns, respectively.
This dataset in from Table 6.9 of Statistical Methods for the Analysis of Repeated Measurements by Charles S. Davis, pp. 161-163 (Springer, 2002). These data are from a multicenter, randomized controlled trial of botulinum toxin type B (BotB) in patients with cervical dystonia from nine U.S. sites.
In [167]:
cdystonia = pd.read_csv("data/cdystonia.csv", index_col=None)
cdystonia.head()
Out[167]:
This dataset includes repeated measurements of the same individuals (longitudinal data). Its possible to present such information in (at least) two ways: showing each repeated measurement in their own row, or in multiple columns representing multiple measurements.
The stack method rotates the data frame so that columns are represented in rows:
In [168]:
stacked = cdystonia.stack()
stacked
Out[168]:
To complement this, unstack pivots from rows back to columns.
In [169]:
stacked.unstack().head()
Out[169]:
For this dataset, it makes sense to create a hierarchical index based on the patient and observation:
In [170]:
cdystonia2 = cdystonia.set_index(['patient','obs'])
cdystonia2.head()
Out[170]:
In [171]:
cdystonia2.index.is_unique
Out[171]:
If we want to transform this data so that repeated measurements are in columns, we can unstack the twstrs measurements according to obs.
In [172]:
twstrs_wide = cdystonia2['twstrs'].unstack('obs')
twstrs_wide.head()
Out[172]:
In [173]:
cdystonia_wide = (cdystonia[['patient','site','id','treat','age','sex']]
.drop_duplicates()
.merge(twstrs_wide, right_index=True, left_on='patient', how='inner')
.head())
cdystonia_wide
Out[173]:
A slightly cleaner way of doing this is to set the patient-level information as an index before unstacking:
In [174]:
(cdystonia.set_index(['patient','site','id','treat','age','sex','week'])['twstrs']
.unstack('week').head())
Out[174]:
To convert our "wide" format back to long, we can use the melt function, appropriately parameterized. This function is useful for DataFrames where one
or more columns are identifier variables (id_vars), with the remaining columns being measured variables (value_vars). The measured variables are "unpivoted" to
the row axis, leaving just two non-identifier columns, a variable and its corresponding value, which can both be renamed using optional arguments.
In [175]:
pd.melt(cdystonia_wide, id_vars=['patient','site','id','treat','age','sex'],
var_name='obs', value_name='twsters').head()
Out[175]:
This illustrates the two formats for longitudinal data: long and wide formats. Its typically better to store data in long format because additional data can be included as additional rows in the database, while wide format requires that the entire database schema be altered by adding columns to every row as data are collected.
The preferable format for analysis depends entirely on what is planned for the data, so it is imporant to be able to move easily between them.
The pivot method allows a DataFrame to be transformed easily between long and wide formats in the same way as a pivot table is created in a spreadsheet. It takes three arguments: index, columns and values, corresponding to the DataFrame index (the row headers), columns and cell values, respectively.
For example, we may want the twstrs variable (the response variable) in wide format according to patient, as we saw with the unstacking method above:
In [176]:
cdystonia.pivot(index='patient', columns='obs', values='twstrs').head()
Out[176]:
If we omit the values argument, we get a DataFrame with hierarchical columns, just as when we applied unstack to the hierarchically-indexed table:
In [177]:
cdystonia.pivot('patient', 'obs')
Out[177]:
A related method, pivot_table, creates a spreadsheet-like table with a hierarchical index, and allows the values of the table to be populated using an arbitrary aggregation function.
In [178]:
cdystonia.pivot_table(index=['site', 'treat'], columns='week', values='twstrs',
aggfunc=max).head(20)
Out[178]:
For a simple cross-tabulation of group frequencies, the crosstab function (not a method) aggregates counts of data according to factors in rows and columns. The factors may be hierarchical if desired.
In [179]:
pd.crosstab(cdystonia.sex, cdystonia.site)
Out[179]:
In [180]:
vessels.duplicated(subset='names')
Out[180]:
In [181]:
vessels.drop_duplicates(['names'])
Out[181]:
In [182]:
cdystonia.treat.value_counts()
Out[182]:
A logical way to specify these numerically is to change them to integer values, perhaps using "Placebo" as a baseline value. If we create a dict with the original values as keys and the replacements as values, we can pass it to the map method to implement the changes.
In [183]:
treatment_map = {'Placebo': 0, '5000U': 1, '10000U': 2}
In [184]:
cdystonia['treatment'] = cdystonia.treat.map(treatment_map)
cdystonia.treatment
Out[184]:
Alternately, if we simply want to replace particular values in a Series or DataFrame, we can use the replace method.
An example where replacement is useful is dealing with zeros in certain transformations. For example, if we try to take the log of a set of values:
In [185]:
vals = pd.Series([float(i)**10 for i in range(10)])
vals
Out[185]:
In [186]:
np.log(vals)
Out[186]:
In such situations, we can replace the zero with a value so small that it makes no difference to the ensuing analysis. We can do this with replace.
In [187]:
vals = vals.replace(0, 1e-6)
np.log(vals)
Out[187]:
We can also perform the same replacement that we used map for with replace:
In [188]:
cdystonia2.treat.replace({'Placebo': 0, '5000U': 1, '10000U': 2})
Out[188]:
For some statistical analyses (e.g. regression models or analyses of variance), categorical or group variables need to be converted into columns of indicators--zeros and ones--to create a so-called design matrix. The Pandas function get_dummies (indicator variables are also known as dummy variables) makes this transformation straightforward.
Let's consider the DataFrame containing the ships corresponding to the transit segments on the eastern seaboard. The type variable denotes the class of vessel; we can create a matrix of indicators for this. For simplicity, lets filter out the 5 most common types of ships:
In [189]:
top5 = vessels.type.isin(vessels.type.value_counts().index[:5])
top5.head(10)
Out[189]:
In [190]:
vessels5 = vessels[top5]
In [191]:
pd.get_dummies(vessels5.type).head(10)
Out[191]:
Pandas' cut function can be used to group continuous or countable data in to bins. Discretization is generally a very bad idea for statistical analysis, so use this function responsibly!
Lets say we want to bin the ages of the cervical dystonia patients into a smaller number of groups:
In [192]:
cdystonia.age.describe()
Out[192]:
Let's transform these data into decades, beginnnig with individuals in their 20's and ending with those in their 80's:
In [193]:
pd.cut(cdystonia.age, [20,30,40,50,60,70,80,90])[:30]
Out[193]:
The parentheses indicate an open interval, meaning that the interval includes values up to but not including the endpoint, whereas the square bracket is a closed interval, where the endpoint is included in the interval. We can switch the closure to the left side by setting the right flag to False:
In [194]:
pd.cut(cdystonia.age, [20,30,40,50,60,70,80,90], right=False)[:30]
Out[194]:
Since the data are now ordinal, rather than numeric, we can give them labels:
In [195]:
pd.cut(cdystonia.age, [20,40,60,80,90], labels=['young','middle-aged','old','really old'])[:30]
Out[195]:
A related function qcut uses empirical quantiles to divide the data. If, for example, we want the quartiles -- (0-25%], (25-50%], (50-70%], (75-100%] -- we can just specify 4 intervals, which will be equally-spaced by default:
In [196]:
pd.qcut(cdystonia.age, 4)[:30]
Out[196]:
Alternatively, one can specify custom quantiles to act as cut points:
In [197]:
quantiles = pd.qcut(segments.seg_length, [0, 0.01, 0.05, 0.95, 0.99, 1])
quantiles[:30]
Out[197]:
Note that you can easily combine discretiztion with the generation of indicator variables shown above:
In [198]:
pd.get_dummies(quantiles).head(10)
Out[198]:
One of the most powerful features of Pandas is its GroupBy functionality. On occasion we may want to perform operations on groups of observations within a dataset. For exmaple:
In [199]:
cdystonia_grouped = cdystonia.groupby(cdystonia.patient)
This grouped dataset is hard to visualize
In [200]:
cdystonia_grouped
Out[200]:
However, the grouping is only an intermediate step; for example, we may want to iterate over each of the patient groups:
In [201]:
for patient, group in cdystonia_grouped:
print('patient', patient)
print('group', group)
A common data analysis procedure is the split-apply-combine operation, which groups subsets of data together, applies a function to each of the groups, then recombines them into a new data table.
For example, we may want to aggregate our data with with some function.
We can aggregate in Pandas using the aggregate (or agg, for short) method:
In [202]:
cdystonia_grouped.agg(np.mean).head()
Out[202]:
Notice that the treat and sex variables are not included in the aggregation. Since it does not make sense to aggregate non-string variables, these columns are simply ignored by the method.
Some aggregation functions are so common that Pandas has a convenience method for them, such as mean:
In [203]:
cdystonia_grouped.mean().head()
Out[203]:
The add_prefix and add_suffix methods can be used to give the columns of the resulting table labels that reflect the transformation:
In [204]:
cdystonia_grouped.mean().add_suffix('_mean').head()
Out[204]:
In [205]:
# The median of the `twstrs` variable
cdystonia_grouped['twstrs'].quantile(0.5)
Out[205]:
If we wish, we can easily aggregate according to multiple keys:
In [206]:
cdystonia.groupby(['week','site']).mean().head()
Out[206]:
Alternately, we can transform the data, using a function of our choice with the transform method:
In [207]:
normalize = lambda x: (x - x.mean())/x.std()
cdystonia_grouped.transform(normalize).head()
Out[207]:
It is easy to do column selection within groupby operations, if we are only interested split-apply-combine operations on a subset of columns:
In [208]:
cdystonia_grouped['twstrs'].mean().head()
Out[208]:
In [209]:
# This gives the same result as a DataFrame
cdystonia_grouped[['twstrs']].mean().head()
Out[209]:
If you simply want to divide your DataFrame into chunks for later use, its easy to convert them into a dict so that they can be easily indexed out as needed:
In [210]:
chunks = dict(list(cdystonia_grouped))
In [211]:
chunks[4]
Out[211]:
By default, groupby groups by row, but we can specify the axis argument to change this. For example, we can group our columns by dtype this way:
In [212]:
dict(list(cdystonia.groupby(cdystonia.dtypes, axis=1)))
Out[212]:
Its also possible to group by one or more levels of a hierarchical index. Recall cdystonia2, which we created with a hierarchical index:
In [213]:
cdystonia2.head(10)
Out[213]:
In [214]:
cdystonia2.groupby(level='obs', axis=0)['twstrs'].mean()
Out[214]:
The function below takes a DataFrame and a column name, sorts by the column, and takes the n largest values of that column. We can use this with apply to return the largest values from every group in a DataFrame in a single call.
In [215]:
def top(df, column, n=5):
return df.sort_index(by=column, ascending=False)[:n]
To see this in action, consider the vessel transit segments dataset (which we merged with the vessel information to yield segments_merged). Say we wanted to return the 3 longest segments travelled by each ship:
In [216]:
top3segments = segments_merged.groupby('mmsi').apply(top, column='seg_length', n=3)[['names', 'seg_length']]
top3segments.head(15)
Out[216]:
Notice that additional arguments for the applied function can be passed via apply after the function name. It assumes that the DataFrame is the first argument.
In [116]:
from IPython.core.display import HTML
HTML(filename='data/titanic.html')
Out[116]:
Women and children first?
groupby method to calculate the proportion of passengers that survived by sex.
In [ ]:
matplotlib is a relatively low-level plotting package, relative to others. It makes very few assumptions about what constitutes good layout (by design), but has a lot of flexiblility to allow the user to completely customize the look of the output.
On the other hand, Pandas includes methods for DataFrame and Series objects that are relatively high-level, and that make reasonable assumptions about how the plot should look.
In [219]:
normals = pd.Series(np.random.normal(size=10))
normals.plot()
Out[219]:
Notice that by default a line plot is drawn, and a light grid is included. All of this can be changed, however:
In [220]:
normals.cumsum().plot(grid=False)
Out[220]:
Similarly, for a DataFrame:
In [221]:
variables = pd.DataFrame({'normal': np.random.normal(size=100),
'gamma': np.random.gamma(1, size=100),
'poisson': np.random.poisson(size=100)})
variables.cumsum(0).plot()
Out[221]:
As an illustration of the high-level nature of Pandas plots, we can split multiple series into subplots with a single argument for plot:
In [222]:
variables.cumsum(0).plot(subplots=True, grid=False)
Out[222]:
Or, we may want to have some series displayed on the secondary y-axis, which can allow for greater detail and less empty space:
In [223]:
variables.cumsum(0).plot(secondary_y='normal', grid=False)
Out[223]:
If we would like a little more control, we can use matplotlib's subplots function directly, and manually assign plots to its axes:
In [225]:
import matplotlib.pyplot as plt
fig, axes = plt.subplots(nrows=1, ncols=3, figsize=(12, 4))
for i,var in enumerate(['normal','gamma','poisson']):
variables[var].cumsum(0).plot(ax=axes[i], title=var)
axes[0].set_ylabel('cumulative sum')
Out[225]:
In [227]:
titanic = pd.read_excel("data/titanic.xls", "titanic")
titanic.head()
Out[227]:
In [228]:
titanic.groupby('pclass').survived.sum().plot(kind='bar')
Out[228]:
In [229]:
titanic.groupby(['sex','pclass']).survived.sum().plot(kind='barh')
Out[229]:
In [230]:
death_counts = pd.crosstab([titanic.pclass, titanic.sex], titanic.survived.astype(bool))
death_counts.plot(kind='bar', stacked=True, color=['black','gold'], grid=False)
Out[230]:
Another way of comparing the groups is to look at the survival rate, by adjusting for the number of people in each group.
In [231]:
death_counts.div(death_counts.sum(1).astype(float), axis=0).plot(kind='barh', stacked=True, color=['black','gold'])
Out[231]:
Frequenfly it is useful to look at the distribution of data before you analyze it. Histograms are a sort of bar graph that displays relative frequencies of data values; hence, the y-axis is always some measure of frequency. This can either be raw counts of values or scaled proportions.
For example, we might want to see how the fares were distributed aboard the titanic:
In [232]:
titanic.fare.hist(grid=False)
Out[232]:
The hist method puts the continuous fare values into bins, trying to make a sensible décision about how many bins to use (or equivalently, how wide the bins are). We can override the default value (10):
In [233]:
titanic.fare.hist(bins=30)
Out[233]:
There are algorithms for determining an "optimal" number of bins, each of which varies somehow with the number of observations in the data series.
In [234]:
sturges = lambda n: int(np.log2(n) + 1)
square_root = lambda n: int(np.sqrt(n))
from scipy.stats import kurtosis
doanes = lambda data: int(1 + np.log(len(data)) + np.log(1 + kurtosis(data) * (len(data) / 6.) ** 0.5))
n = len(titanic)
sturges(n), square_root(n), doanes(titanic.fare.dropna())
Out[234]:
In [235]:
titanic.fare.hist(bins=doanes(titanic.fare.dropna()))
Out[235]:
A density plot is similar to a histogram in that it describes the distribution of the underlying data, but rather than being a pure empirical representation, it is an estimate of the underlying "true" distribution. As a result, it is smoothed into a continuous line plot. We create them in Pandas using the plot method with kind='kde', where kde stands for kernel density estimate.
In [236]:
titanic.fare.dropna().plot(kind='kde', xlim=(0,600))
Out[236]:
Often, histograms and density plots are shown together:
In [237]:
titanic.fare.hist(bins=doanes(titanic.fare.dropna()), normed=True, color='lightseagreen')
titanic.fare.dropna().plot(kind='kde', xlim=(0,600), style='r--')
Out[237]:
Here, we had to normalize the histogram (normed=True), since the kernel density is normalized by definition (it is a probability distribution).
We will explore kernel density estimates more in the next section.
In [238]:
titanic.boxplot(column='fare', by='pclass', grid=False)
Out[238]:
You can think of the box plot as viewing the distribution from above. The blue crosses are "outlier" points that occur outside the extreme quantiles.
One way to add additional information to a boxplot is to overlay the actual data; this is generally most suitable with small- or moderate-sized data series.
In [239]:
bp = titanic.boxplot(column='age', by='pclass', grid=False)
for i in [1,2,3]:
y = titanic.age[titanic.pclass==i].dropna()
# Add some random "jitter" to the x-axis
x = np.random.normal(i, 0.04, size=len(y))
plt.plot(x, y.values, 'r.', alpha=0.2)
When data are dense, a couple of tricks used above help the visualization:
In [ ]:
In [240]:
baseball = pd.read_csv("data/baseball.csv")
baseball.head()
Out[240]:
Scatterplots are useful for data exploration, where we seek to uncover relationships among variables. There are no scatterplot methods for Series or DataFrame objects; we must instead use the matplotlib function scatter.
In [241]:
plt.scatter(baseball.ab, baseball.h)
plt.xlim(0, 700); plt.ylim(0, 200)
Out[241]:
We can add additional information to scatterplots by assigning variables to either the size of the symbols or their colors.
In [242]:
plt.scatter(baseball.ab, baseball.h, s=baseball.hr*10, alpha=0.5)
plt.xlim(0, 700); plt.ylim(0, 200)
Out[242]:
In [243]:
plt.scatter(baseball.ab, baseball.h, c=baseball.hr, s=40, cmap='hot')
plt.xlim(0, 700); plt.ylim(0, 200);
To view scatterplots of a large numbers of variables simultaneously, we can use the scatter_matrix function that was recently added to Pandas. It generates a matrix of pair-wise scatterplots, optiorally with histograms or kernel density estimates on the diagonal.
In [244]:
_ = pd.scatter_matrix(baseball.loc[:,'r':'sb'], figsize=(12,8), diagonal='kde')